﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Lg.Core.DbHelper;

namespace Lg.Core.Extensions.DbHelper
{
    public static class DBHelperExtensions
    {

        #region [inset]
        /// <summary>
        /// 范型实体插入数据库方法
        /// 不指定字段参数
        /// 排除值为null和标记特性为Grow的自增字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dBHelper"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int Insert<T>(this IDBHelper dBHelper, T t) where T : IDBModel
        {
            Type _type = typeof(T);
            PropertyInfo[] _usePropertyInfos = _type.GetProperties().Where(p => p.GetValue(t) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray(); ;
            if (_usePropertyInfos.Length > 0)
            {
                string sql = $"Insert into [{_type.Name}] ({string.Join(",", _usePropertyInfos.Select(p => p.Name))}) values({string.Join(",", _usePropertyInfos.Select(p => "@" + p.Name))})";
                List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(t)!)).ToList();
                return dBHelper.ExecuteNonQuery(sql, listParam);
            }
            else
            {
                throw new Exception("未提供有效的可插入字段");
            }
        }

        /// <summary>
        /// 范型接口扩展方法，插入数据
        /// 指定字段参数，字段参数的处理方式，'include(指定包含)'或'exclude(指定排除)'
        /// 排除值为null和含Grow特性的自增字段
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="dBHelper">扩展对像实例</param>
        /// <param name="t">范型类型参数</param>
        /// <param name="option">fields字段参数的处理方式，'include(指定包含)'或'exclude(指定排除)',</param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static int Insert<T>(this IDBHelper dBHelper, T t, FieldOption option, params string[] fields) where T : IDBModel
        {
            Type _type = typeof(T);
            //获取可用插入属性数组(将空值和自增字段排除)
            PropertyInfo[] _usePropertyInfos = _type.GetProperties().Where(p => p.GetValue(t) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
            if (option == FieldOption.Include)
            {
                _usePropertyInfos = _usePropertyInfos.Where(p => fields.Contains(p.Name)).ToArray();
            }
            else
            {
                _usePropertyInfos = _usePropertyInfos.Where(p => !fields.Contains(p.Name)).ToArray();
            }

            string sql = $"Insert into [{_type.Name}] ({string.Join(",", _usePropertyInfos.Select(p => p.Name))}) values({string.Join(",", _usePropertyInfos.Select(p => "@" + p.Name))})";
            List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(t)!)).ToList();
            return dBHelper.ExecuteNonQuery(sql, listParam);
        }
        #endregion    

        #region [eidt] 
        /// <summary>
        /// 范型数据库插入方法
        /// 不指定字段参数(排除值为null和自增特性的属性)
        /// 实体内必须包含特性为 Identity的属性
        /// </summary>
        /// <param name="model"></param>
        /// <param name="dBHelper"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int Edit<T>(this IDBHelper dBHelper, T t) where T : IDBModel
        {
            Type _type = typeof(T);
            PropertyInfo[] _propertyInfos = _type.GetProperties();
            PropertyInfo[] _usePropertyInfos = _propertyInfos.Where(p => p.GetValue(t) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray(); ;
            PropertyInfo? _identityPropertyInfo = _propertyInfos.FirstOrDefault(p => p.GetValue(t) != null && p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("IdentifyAttribute")).Any());
            if (_identityPropertyInfo != null)
            {
                if (_usePropertyInfos.Length > 0)
                {
                    string sql = $"update  [{_type.Name}] set {string.Join(",", _usePropertyInfos.Select(p => $"[{p.Name}]=@{p.Name}"))} where {_identityPropertyInfo.Name}=@{_identityPropertyInfo.Name}";
                    List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(t) ?? DBNull.Value)).ToList();
                    listParam.Add(new DBParameter(_identityPropertyInfo.Name, _identityPropertyInfo.GetValue(t)!));
                    return dBHelper.ExecuteNonQuery(sql, listParam);
                }
                else
                {
                    throw new Exception("未提供有效的可更新字段！");
                }

            }
            else
            {
                throw new Exception($"未找到类型:[{_type.Name}]中包含[Identity]特性且值不为null的属性，无法构造where条件");
            }
        }
        /// <summary>
        /// 范型单行数据更新
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="dBHelper">扩展对像实例</param>
        /// <param name="t">范型类型参数</param>
        /// <param name="option">fields字段参数的处理方式，'include(指定包含)'或'exclude(指定排除)',</param>
        /// <param name="fields">待处理的字段</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int Edit<T>(this IDBHelper dBHelper, T t, FieldOption option, params string[] fields) where T : IDBModel
        {
            Type _type = typeof(T);
            PropertyInfo[] _propertyInfos = _type.GetProperties();//原始属性集合
            PropertyInfo[] _usePropertyInfos;//最终符合条件的更新属性集合
            PropertyInfo? _identityPropertyInfo = _propertyInfos.FirstOrDefault(p => p.GetValue(t) != null && p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("IdentifyAttribute")).Any());
            if (_identityPropertyInfo != null)
            {
                if (option == FieldOption.Include)
                {
                    _usePropertyInfos = _propertyInfos.Where(p => fields.Contains(p.Name) && p.GetValue(t) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
                }
                else
                {
                    _usePropertyInfos = _propertyInfos.Where(p => !fields.Contains(p.Name) && p.GetValue(t) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
                }
                string sql = $"update  [{_type.Name}] set {string.Join(",", _usePropertyInfos.Select(p => $"[{p.Name}]=@{p.Name}"))} where {_identityPropertyInfo.Name}=@{_identityPropertyInfo.Name}";
                List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(t) ?? DBNull.Value)).ToList();
                listParam.Add(new DBParameter(_identityPropertyInfo.Name, _identityPropertyInfo.GetValue(t)!));
                return dBHelper.ExecuteNonQuery(sql, listParam);
            }
            else
            {
                throw new Exception($"未找到类型:[{_type.Name}]中包含[Identity]特性且值不为null的属性，无法构造where条件");
            }

        }

        /// <summary>
        /// 范型编辑
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="listParam"></param>
        /// <returns></returns>
        public static int Edit(this IDBHelper dBHelper, string sql, List<DBParameter>? DBParams = null)
        {
            return dBHelper.ExecuteNonQuery(sql, DBParams);
        }
        #endregion

        #region [select]
        /// <summary>
        /// 范型单表通用数据库查询方法
        /// </summary>
        /// <typeparam name="T">范型类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="listParam">参数集合，无则给null</param>
        /// <returns></returns>
        public static DataTable? Select(this IDBHelper dBHelper, string sql, List<DBParameter>? DBParams = null)
        {
            return dBHelper.Query(sql, DBParams).Tables[0];
        }
        public static T? Select<T>(this IDBHelper dBHelper, string sql, List<DBParameter>? DBParams = null) where T : new()
        {
            DataTable? dt = null;
            dt = dBHelper.Query(sql, DBParams).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                T model = new T();
                Type type = model.GetType();
                DataRow dr = dt.Rows[0];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = type.GetProperty(dt.Columns[i].ColumnName)!;
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                    {
                        try
                        {
                            propertyInfo.SetValue(model, dr[i]);
                        }
                        catch
                        {
                            if (propertyInfo.PropertyType.Name == "String")
                            {
                                propertyInfo.SetValue(model, dr[i].ToString());
                            }
                        }
                    }

                }
                return model;
            }
            else
            {
                return default;
            }
        }
        /// <summary>
        /// 获取list实体集合
        /// </summary>
        /// <typeparam name="T">范型类型</typeparam>
        /// <param name="sql">sql字串</param>
        /// <param name="listParam">参数集合，无则为null</param>
        /// <returns></returns>
        public static List<T>? SelectList<T>(this IDBHelper dBHelper, string sql, List<DBParameter>? DBParams=null) where T : new()
        {
            DataTable? dt = null;
            dt = dBHelper.Query(sql, DBParams).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                List<T> list = new List<T>();
                foreach (DataRow dr in dt.Rows)
                {
                    T model = new T();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        PropertyInfo propertyInfo = model.GetType().GetProperty(dt.Columns[i].ColumnName)!;
                        if (propertyInfo != null && dr[i] != DBNull.Value)
                        {
                            try
                            {
                                propertyInfo.SetValue(model, dr[i]);
                            }
                            catch
                            {

                            }
                        }
                    }
                    list.Add(model);
                }
                return list;
            }
            else
            {
                return null;
            }


        }
        /// <summary>
        /// 获取单值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listParam">参数集合,可为null</param>
        /// <returns></returns>
        public static object SelectValue(this IDBHelper dBHelper, string sql, List<DBParameter>? DBParams = null)
        {
            return dBHelper.ExecuteScalar(sql, DBParams);
        }

        public static DataTable Select(this IDBHelper dBHelper, string sql, List<DBParameter> DBParams, int page, int limit)
        {
            sql += $" offset  {(page - 1) * limit} rows fetch next {limit} rows only";
            return dBHelper.Query(sql, DBParams).Tables[0];
        }
        #endregion

    }
}
